--    Program: 		CAPARIMP_CUSTOMER_PARTY_SITE_USE.sql
--    Description:  Import AR CUSTOMER PARTY SITE USE information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_party_site_use_id		number;
                       		                 		
   cursor cur_party_site_use is
      select * from CRST_HZ_PARTY_SITE_USES
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_party_site_use loop   	
     l_err_msg_tmp := '';
     
     select HZ_PARTY_SITE_USES_S.NEXTVAL 
     INTO l_party_site_use_id
     FROM DUAL;
     
     update CRST_HZ_PARTY_SITE_USES
     set    party_site_use_id_n = l_party_site_use_id
     where  party_site_use_id = cur.party_site_use_id; 
     
     begin
       -- import data
       insert into HZ_PARTY_SITE_USES
       (
         PARTY_SITE_USE_ID       ,
         BEGIN_DATE              ,
         COMMENTS                ,
         END_DATE                ,
         PARTY_SITE_ID           ,
         LAST_UPDATE_DATE        ,
         REQUEST_ID              ,
         LAST_UPDATED_BY         ,
         PROGRAM_APPLICATION_ID  ,
         CREATION_DATE           ,
         PROGRAM_ID              ,
         CREATED_BY              ,
         PROGRAM_UPDATE_DATE     ,
         LAST_UPDATE_LOGIN       ,
         WH_UPDATE_DATE          ,
         SITE_USE_TYPE           ,
         PRIMARY_PER_TYPE        ,
         STATUS                  ,
         OBJECT_VERSION_NUMBER   ,
         CREATED_BY_MODULE       ,
         APPLICATION_ID               
       )
       values
       (
         l_party_site_use_id         ,
         cur.BEGIN_DATE              ,
         cur.COMMENTS                ,
         cur.END_DATE                ,
         CAPARIMP_MAPPING.GetHZPartySiteIDMapping(cur.PARTY_SITE_ID),
         cur.LAST_UPDATE_DATE        ,
         cur.REQUEST_ID              ,
         cur.LAST_UPDATED_BY         ,
         cur.PROGRAM_APPLICATION_ID  ,
         cur.CREATION_DATE           ,
         cur.PROGRAM_ID              ,
         cur.CREATED_BY              ,
         cur.PROGRAM_UPDATE_DATE     ,
         cur.LAST_UPDATE_LOGIN       ,
         cur.WH_UPDATE_DATE          ,
         cur.SITE_USE_TYPE           ,
         cur.PRIMARY_PER_TYPE        ,
         cur.STATUS                  ,
         cur.OBJECT_VERSION_NUMBER   ,
         cur.CREATED_BY_MODULE       ,
         cur.APPLICATION_ID          
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_PARTY_SITE_USE', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
